package com.red.wood.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.jolbox.bonecp.BoneCPDataSource;

public class JdbcUtil {
	private static final Log log = LogFactory.getLog(JdbcUtil.class);
	private static BoneCPDataSource isdmsOracleBoneCP = null;
	static {
		try {
			SysProps props = SysProps.getInstance();
			isdmsOracleBoneCP = new BoneCPDataSource();
			isdmsOracleBoneCP.setDriverClass(props.getValue("jdbc.isdms.oracle.driverClass"));
			isdmsOracleBoneCP.setJdbcUrl(props.getValue("jdbc.isdms.oracle.url"));
			isdmsOracleBoneCP.setUser(props.getValue("jdbc.isdms.oracle.username"));
			isdmsOracleBoneCP.setPassword(props.getValue("jdbc.isdms.oracle.password"));
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			throw new RuntimeException(e.getMessage(), e);
		}
	}
	
	/**
	 * 从JDBC中获得数据库连接
	 * 
	 * @return
	 */
	public static Connection getConnection() {
		Connection con = null;
		try {
			con =  isdmsOracleBoneCP.getConnection();
		} catch (Exception e) {
			if(log.isErrorEnabled()){
				log.error(e.getMessage(), e);
			}
			throw new RuntimeException(e.getMessage(), e);
		} 
		return con;
	}
	
	@Override
	protected void finalize() throws Throwable {
		isdmsOracleBoneCP.close();
		super.finalize();
	}

	/**
	 * 释放数据库连接
	 * 
	 * @param conn
	 */
	public static void close(Connection conn) {
		if (conn == null) {
			return;
		}
		try {
			if (conn.isClosed()) {
				return;
			}
			conn.close();
		} catch (SQLException e) {
			if(log.isErrorEnabled()){
				log.error(e.getMessage(), e);
			}
		}
	}

	/**
	 * 关闭STMT
	 * @param stmt
	 */
	public static void close(Statement stmt) {
		if(stmt == null){
			return;
		}
		try {
			stmt.close();
		} catch (SQLException e) {
			if(log.isErrorEnabled()){
				log.error(e.getMessage(), e);
			}
		}
	}

	/**
	 * 关闭RS
	 * 
	 * @param rs
	 */
	public static void close(ResultSet rs) {
		if(rs == null){
			return;
		}
		try {
			rs.close();
		} catch (SQLException e) {
			if(log.isErrorEnabled()){
				log.error(e.getMessage(), e);
			}
		}
	}

	/**
	 * 关闭STMT和CONN
	 * @param stmt
	 * @param conn
	 */
	public static void close(Statement stmt, Connection conn) {
		close(stmt);
		close(conn);
	}

	/**
	 * 关闭RS、STMT和CONN
	 * 
	 * @param rs
	 * @param stmt
	 * @param conn
	 */
	public static void close(ResultSet rs, Statement stmt, Connection conn) {
		close(rs);
		close(stmt);
		close(conn);
	}
	
	/**
	 * 判断当前操作系统是不是window
	 * 
	 * @return boolean
	 */
	public static boolean isWindows() {
		boolean flag = false;
		if (System.getProperties().getProperty("os.name").toUpperCase().indexOf("WINDOWS") != -1) {
			flag = true;
		}
		return flag;
	}
	
	public static boolean execSql(Connection conn,String sql){
    	boolean flag = true;
    	PreparedStatement ps = null;
    	try{
    		conn.setAutoCommit(false);
    		ps = conn.prepareStatement(sql);
          	ps.executeQuery();
            conn.commit();
          	flag = true;
          }catch(SQLException e){
         	 flag = false;
              try {
                  conn.rollback();
              } catch (SQLException e1) {
            	  e1.printStackTrace();
              }
          }finally{
        	  JdbcUtil.close(ps);
          }
    	return flag ;
    }
	
	public static List<Map<String, Object>> resultSetToList(ResultSet rs) throws SQLException{
		if (rs == null) return new ArrayList<Map<String, Object>>();
		ResultSetMetaData md = rs.getMetaData(); // 得到结果集(rs)的结构信息，比如字段数、字段名等
		int columnCount = md.getColumnCount(); // 返回此 ResultSet 对象中的列数
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Map<String, Object> rowData = new HashMap<String, Object>();
		while (rs.next()) {
			rowData = new HashMap<String, Object>(columnCount);
			for (int i = 1; i <= columnCount; i++) {
				rowData.put(md.getColumnName(i), rs.getObject(i));
			}
			list.add(rowData);
		}
		return list;
	}
	
	/**
	 * 批量插入
	 * @param list
	 * @param sql : insert into tbl_device(mainDevice_id,device_type,device_id,device_ip,device_port) values (?,?,?,?,?)
	 * @param columns : mainDevice_id,device_type,device_id,device_ip,device_port
	 * @param size
	 */
	public static void insertBatchTableData(Connection conn,List<Map<String, Object>> list,String sql, String columns,int size) {
		String[] columnsSplit = columns.split(","); 
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			conn.setAutoCommit(false);
			int n = 0;
			for (int i = 0; i < list.size(); i++) {
				Map<String, Object> map = list.get(i);
				for (int j = 0; j < columnsSplit.length; j++) {
					ps.setObject(j + 1, map.get(columnsSplit[j]));
				}
				ps.addBatch();
				if (++n % size == 0) {
					ps.executeBatch();
					conn.commit();
				}
			}
			ps.executeBatch();
			conn.commit();
		}catch(Exception e){
			if(log.isErrorEnabled()){
				log.error(e.getMessage(),e);
			}
		}finally{
			JdbcUtil.close(ps);
		}
	}
}